//  根据工单导出司机上班考勤


const fs = require("fs");
const XLSX = require("xlsx");
const moment = require("moment");
const iconv = require("iconv-lite");

// 读取Excel文件
const workbook = XLSX.readFile("checkingIn/data.xlsx");
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// 将Excel数据转换为JSON格式
// const data = XLSX.utils.sheet_to_json(worksheet);
const data = XLSX.utils.sheet_to_json(worksheet, { raw: false, dateNF: 'yyyy/m/d' });

const days = 19 // 需要统计的天数
let header = ['司机','车队','出勤天数']
let dateArr = [];

for (let i = 1; i <= days; i++) {
    header.push(i)
    dateArr[i-1] = '';
}

const attendance = {
    // '周国友': { driver:'', team:'', days:[] }
}
;
data.forEach((row) => {
    // console.log(row)
    const driver = row["司机"];
    const team = row["车队"];
    const day =  row["日期"].split('/')[1];
    // console.log(day,'day')
    if(driver){
        if(attendance[driver]){
            const oneTeam = attendance[driver].team
            const oneDays = attendance[driver].days
            oneDays[day-1] = '√'
            let teamtemp = oneTeam
            if(!oneTeam.includes(team)){
                teamtemp += `、${team}`
            }
            attendance[driver] = { driver: driver, team: teamtemp, days: oneDays }
    
        }else{
            const dateArrRow = JSON.parse(JSON.stringify(dateArr))
            dateArrRow[day-1] = '√'
            attendance[driver] = { driver: driver, team: team, days: dateArrRow }
        }
    }

});

const outputObj = {
    
}

// 相同车队的放一起
const outputData = Object.values(attendance).map(item => {
    let row1 = [item.driver, item.team, item.days.filter(day => day !== '').length, ...item.days];
    if(outputObj[item.team]){
        outputObj[item.team].push(row1)
    }else{
        outputObj[item.team] = [row1] 
    }
    return row1
});

// 根据对象中key值进行排序，'、'多的排后面
const sortedOutputObj = Object.fromEntries(
    Object.entries(outputObj).sort((a, b) => {
        const teamA = a[0];
        const teamB = b[0];
        return teamA.split('、').length - teamB.split('、').length;
    })
);

// 将values中的二维数组转化成一维数组
const flattenedOutputData = Object.values(sortedOutputObj).flat();
// console.log(JSON.stringify(flattenedOutputData),'flattenedOutputData')


// 创建Excel工作簿
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([header, ...flattenedOutputData]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
// 将工作簿写入文件
XLSX.writeFile(wb, `checkingIn/statistics/5月份考勤.xlsx`);